--
-- Create Table    : 'Bil'   
-- Model           :  
-- Type            :  
-- SerieNr         :  
-- RegNummer       :  
--
CREATE TABLE Bil (
    Model          VARCHAR(50) NOT NULL,
    Type           VARCHAR(50) NOT NULL,
    SerieNr        VARCHAR(100) NOT NULL,
    RegNummer      VARCHAR(100) NOT NULL,
CONSTRAINT pk_Bil PRIMARY KEY CLUSTERED (SerieNr))
GO

--
-- Create Table    : 'Garage'   
-- Vejnavn         :  
-- Nummer          :  
-- ByNavn          :  
-- Postnummer      :  
-- GarageID        :  
--
CREATE TABLE Garage (
    Vejnavn        VARCHAR(100) NOT NULL,
    Nummer         VARCHAR(20) NOT NULL,
    ByNavn         VARCHAR(20) NOT NULL,
    Postnummer     VARCHAR(20) NOT NULL,
    GarageID       BIGINT IDENTITY(1,1) NOT NULL,
CONSTRAINT pk_Garage PRIMARY KEY CLUSTERED (GarageID))
GO

--
-- Create Table    : 'Chauffoer'   
-- Navn            :  
-- Koerekortnr     :  
-- CPRNr           :  
-- SerieNr         :  (references Bil.SerieNr)
--
CREATE TABLE Chauffoer (
    Navn           VARCHAR(50) NOT NULL,
    Koerekortnr    VARCHAR(50) NOT NULL,
    CPRNr          VARCHAR(25) NOT NULL,
    SerieNr        VARCHAR(100) NULL,
CONSTRAINT pk_Chauffoer PRIMARY KEY CLUSTERED (Koerekortnr),
CONSTRAINT fk_Chauffoer FOREIGN KEY (SerieNr)
    REFERENCES Bil (SerieNr)
    ON UPDATE CASCADE)
GO

--
-- Create Table    : 'StaerI'   
-- SerieNr         :  (references Bil.SerieNr)
-- GarageID        :  (references Garage.GarageID)
--
CREATE TABLE StaerI (
    SerieNr        VARCHAR(100) NOT NULL,
    GarageID       BIGINT NOT NULL,
CONSTRAINT pk_StaerI PRIMARY KEY CLUSTERED (SerieNr,GarageID),
CONSTRAINT fk_StaerI FOREIGN KEY (SerieNr)
    REFERENCES Bil (SerieNr)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
CONSTRAINT fk_StaerI2 FOREIGN KEY (GarageID)
    REFERENCES Garage (GarageID)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
GO
